#!/bin/sh
#抽取日表数据到dws_urea_so_sar_pdi表中
CUR_DIR=$(cd "$(dirname "${BASH_SOURCE-$0}")"; pwd)
UP_DIR=$(dirname $(dirname $(dirname "$PWD")))
echo ${UP_DIR}
source ${UP_DIR}/system_env.sh


START_TIME=$(date +%s)
hive_db=${hive_db}

if [ -z "${hive_db}" ];then
  hive_db=dataplatform_trip
else
  hive_db=${hive_db}
fi


if [ -z "$1" ];then
  part_time=$(date -d "1 days ago" +%Y%m%d)
else
  part_time=$1
fi

sql_suffix=$(cat <<EOF
  insert overwrite table ${hive_db}.dws_urea_so_sar_pdi
   select
                                a.tid                       ,
                                a.mileage_so_far - nvl(b.firstmileage,0) as mileage_so_far,
                                a.fuel_so_far - nvl(b.firstfuel,0)  as fuel_so_far ,
                                round(100*(a.fuel_so_far - nvl(b.firstfuel,0))/(a.mileage_so_far - nvl(b.firstmileage,0)),0) as fuel_100km_so_far,
                                urea_consume+urea_day as urea_consume,
                                round(100*(urea_consume+urea_day)/(a.mileage_so_far - b.firstmileage),0)  as urea_100km ,
                                round(100*(urea_consume+urea_day)/(a.mileage_so_far - b.firstmileage),0) as urea_fuel_ratio,
                                from_unixtime(unix_timestamp() ,'yyyy-MM-dd HH:mm:ss') as update_time  from
                             (SELECT
                                  t1.tid as tid ,
                                  t1.route_start_time as route_start_time ,
                                  nvl(first_value(t1.route_end_mileage) over (partition by t1.tid order by t1.route_start_time desc ),0) as mileage_so_far,
                                  nvl(first_value(t1.route_end_fuel) over (partition by t1.tid order by t1.route_start_time desc ),0) as fuel_so_far,
                                  nvl(sum(t1.trip_urea) over (partition by t1.tid ),0) as urea_day,
                                  nvl(t2.urea_consume,0) as urea_consume,
                                  row_number() over (partition by t1.tid order by t1.route_start_time  desc) row_number
                                 from ${hive_db}.trip_statistics t1
                               left join ${hive_db}.dws_urea_so_sar_pdi t2
                                on (t1.tid = t2.tid)
                                 where t1.day = ${part_time}
                                 and nvl(t1.trip_urea,0) >0 ) a
                               left join ${hive_db}.dws_first_receive_urea_pdi b
                                on (a.tid = b.tid )
                               where row_number = 1;
EOF
)

CUR_DIR=$(cd "$(dirname "$0")"; pwd)
hive_config=$(cat  "${CUR_DIR}"/hive.config)
sql="${hive_config}${sql_suffix}"
echo "导入dws_urea_so_sar_pdi的SQL为：${sql}"

#执行语句·
hive -e "${sql}"
if [ $? -ne 0 ];then
  echo "导入dws_urea_so_sar_pdi失败，耗时$((`date +%s`-START_TIME))秒..."
else
  echo "导入dws_urea_so_sar_pdi成功，耗时$((`date +%s`-START_TIME))秒..."
fi